/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package VLSObjects;

import DBConnection.*;
import java.sql.*;
import VLSObjects.*;

public class ServicesPerTransactionManager {
    
    public boolean addServicePerTransction(ServicesPerTransaction newServicesPerTransaction)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            
            //check first if a similar service is availed for the current transaction
            PreparedStatement pstmt1 = conn.prepareStatement("SELECT servicetransaction_id FROM servicetransaction where transaction_id = ? AND service_name = ? AND service_id = ?");
            pstmt1.setString(i++, newServicesPerTransaction.getTransactionID());
            pstmt1.setString(i++, newServicesPerTransaction.getServiceName());
            pstmt1.setString(i++, newServicesPerTransaction.getServiceID());
            
            ResultSet rs = pstmt1.executeQuery();
            
            while(rs.next())
            {
                result = true; //similar record in database found
            }
            
            
            if(result != true)
            {
                i = 1;

                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO servicetransaction (transaction_id, vehicle_id, service_id, service_name, amount) VALUES(?,?,?,?,?)");

                pstmt.setString(i++, newServicesPerTransaction.getTransactionID());
                pstmt.setString(i++, newServicesPerTransaction.getVehicleID());
                pstmt.setString(i++, newServicesPerTransaction.getServiceID());
                pstmt.setString(i++, newServicesPerTransaction.getServiceName());
                pstmt.setInt(i++, newServicesPerTransaction.getAmount());


                //execute query
                pstmt.executeUpdate();
            }

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public boolean refreshServices(String transactionID)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("DELETE FROM servicetransaction WHERE transaction_id = ?");

            //include parameters
            pstmt.setString(i++, transactionID);

            //execute query
            pstmt.executeUpdate();

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public Integer getRevenue(Date from, Date to)
    {   Integer revenue = 0;
    
         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("SELECT SUM(servicetransaction.amount), servicetransaction.transaction_id FROM servicetransaction LEFT JOIN transaction ON servicetransaction.transaction_id = transaction.transaction_id WHERE transaction.transaction_date BETWEEN " + from + "AND " + to);

            //include parameters
            //pstmt.setString(i++, service);

            //execute query
            ResultSet rs = pstmt.executeQuery();

            //close DB connection
            conn.close();

            revenue = rs.getInt(1);
            
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            //return result;
        
        
        return revenue;
    }
    
}
